﻿using LiteDB;
using LiteDB.Async;
using Mapster;
using System.Diagnostics;
using System.Linq.Expressions;
using WebAppDbTest.Models;

namespace WebAppDbTest.Services;

/// <summary>
/// 天气预报接口规范定义
/// </summary>
public interface IWeatherForecastServices
{
    /// <summary>
    /// 获取天气预报概要
    /// </summary>
    /// <returns></returns>
    string GetSummarie();

    /// <summary>
    /// 获取天气预报列表
    /// </summary>
    /// <param name="count"></param>
    /// <returns></returns>
    IEnumerable<WeatherForecast> GetWeatherForecasts(int count);

    #region about litedb crud
    Task<Guid> LiteDbAddSingleAsync<T>(string collectioName, T t);

    Task<int> LiteDbAddBulkAsync<T>(string collectioName, IEnumerable<T> list);

    Task<T> LiteDbGetSingleAsync<T>(string collectioName, Guid id);

    Task<IEnumerable<T>> LiteDbGetAllAsync<T>(string collectioName);

    Task<bool> LiteDbUpdateSingleAsync<T>(string collectioName, T t);

    Task<int> LiteDbUpdateBulkAsync<T>(string collectioName, IEnumerable<T> list);

    Task<bool> LiteDbDeleteSingleAsync<T>(string collectioName, Guid id);

    Task<int> LiteDbDeleteBulkAsync<T>(string collectioName, Expression<Func<T, bool>> predicate);
    #endregion

    #region about sqlite crud
    Task<Guid> SqliteAddSingleAsync<T>(T t) where T : BaseEntity;

    Task<int> SqliteAddBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity;

    Task<T> SqliteGetSingleAsync<T>(Guid id) where T : BaseEntity;

    Task<IEnumerable<T>> SqliteGetAllAsync<T>() where T : BaseEntity;

    Task<bool> SqliteUpdateSingleAsync<T>(T t) where T : BaseEntity, new();

    Task<int> SqliteUpdateBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity, new();

    Task<bool> SqliteDeleteSingleAsync<T>(Guid id) where T : BaseEntity;

    Task<int> SqliteDeleteBulkAsync<T>(List<Guid> ids) where T : BaseEntity;
    #endregion
}

/// <summary>
/// 天气预报接口规范实现，模拟天气预报的数据
/// </summary>
public class WeatherForecastServices : IWeatherForecastServices
{
    #region 构造函数 DI
    private readonly IAppLogsServices _logger;
    private readonly IConfiguration _configuration;
    private readonly IFreeSql _freeSql;
    private readonly IWebHostEnvironment _webHostEnvironment;

    public WeatherForecastServices(IAppLogsServices logger,
        IConfiguration configuration,
        IFreeSql freeSql,
        IWebHostEnvironment webHostEnvironment)
    {
        _logger = logger;
        _configuration = configuration;
        _freeSql = freeSql;
        _webHostEnvironment = webHostEnvironment;
    }
    #endregion

    #region 模拟数据
    /// <summary>
    /// 模拟天气情况摘要数据列表
    /// </summary>
    private static readonly string[] Summaries = new[]
    {
        "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
    };

    public string GetSummarie() => Summaries[Random.Shared.Next(Summaries.Length)];

    public IEnumerable<WeatherForecast> GetWeatherForecasts(int count)
    {
        if (count <= 0 || count > 1000) count = 1000;

        /** 等效代码如下
        return Enumerable.Range(1, count).Select(index => {
            int temperatureC = Random.Shared.Next(-20, 55);
            var wf = new WeatherForecast
            {
                Id = Guid.NewGuid(),
                //Date = DateOnly.FromDateTime(DateTime.Now.AddDays(index)),
                Date = DateTime.Now.AddDays(index),
                TemperatureC = temperatureC,
                TemperatureF = 32 + (int)(temperatureC / 0.5556),
                Summary = GetSummarie()
            };
            return wf;
        }).ToArray();
        */

        return Enumerable.Range(1, count).Select(index => GetWeatherForecast(index)).ToArray();
    }

    private WeatherForecast GetWeatherForecast(int index) 
    {
        int temperatureC = Random.Shared.Next(-20, 55);
        var wf = new WeatherForecast
        {
            Id = Guid.NewGuid(),
            Date = DateTime.Now.AddDays(index),
            TemperatureC = temperatureC,
            TemperatureF = 32 + (int)(temperatureC / 0.5556),
            Summary = GetSummarie()
        };
        return wf;
    }

    #endregion

    private enum DbFileType { LiteDB, SQLite };

    private string GetConnString(int index, DbFileType dbFileType = DbFileType.LiteDB) 
    {
        string? dbFile = _configuration.GetSection($"DbConfig:{index}:DbFilePath").Value;
        string filePath = Path.Combine(_webHostEnvironment.ContentRootPath, dbFile);

        string dbConnString = string.Empty;
        switch (dbFileType)
        {
            case DbFileType.LiteDB:
                dbConnString = $"Filename={ filePath };Connection=shared;Password=123456";
                break;
            case DbFileType.SQLite:
                dbConnString = $"Data Source={ filePath };Version=3;Pooling=False;Max Pool Size=100";
                break;
            default:
                dbConnString = $"Filename={ filePath };Connection=shared;Password=123456";
                break;
        }

        return dbConnString;
    }

    private static readonly Stopwatch _sw = new();

    /// <summary>
    /// 记录信息
    /// </summary>
    /// <param name="ts">方法执行耗时，单位：毫秒/ms</param>
    /// <param name="appLogs"></param>
    /// <returns></returns>
    private async Task LiteDbWraiteInfoAsync(ActionExecInfo actionExecInfo, AppLogs appLogs)
    {
        // 记录操作方法执行的时间
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<ActionExecInfo>(nameof(ActionExecInfo));
        var item = await collection.InsertAsync(actionExecInfo);
        appLogs.ActionExecInfoId = item.AsGuid;

        // 记录日志
        await _logger.WriteLogAsync(appLogs);
    }

    #region About LiteDb CRUD
    public async Task<Guid> LiteDbAddSingleAsync<T>(string collectioName, T t)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        var item = await collection.InsertAsync(t);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddSingle",
            ItemCount = 1,
            OperationInfo = $"[AddSingle] ==> 插入数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return item.AsGuid;
    }

    public async Task<int> LiteDbAddBulkAsync<T>(string collectioName, IEnumerable<T> list)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个表和 MongoDB 一样的
        var collection = db.GetCollection<T>(collectioName);
        int rcount = await collection.InsertBulkAsync(list);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddBulk",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddBulk",
            ItemCount = 1,
            OperationInfo = $"[AddBulk] ==> 插入数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<T> LiteDbGetSingleAsync<T>(string collectioName, Guid id) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        var result = await collection.FindByIdAsync(id); // 下面代码等效
        // var item = await collection.FindOneAsync(x => x.Id == id);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetSingle",
            ItemCount = 1,
            OperationInfo = $"[GetSingle] ==> 查询数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<IEnumerable<T>> LiteDbGetAllAsync<T>(string collectioName)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        var result = await collection.FindAllAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetAll",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetAll",
            ItemCount = result.Count(),
            OperationInfo = $"[GetAll] ==> 查询数据:{result.Count()}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<bool> LiteDbUpdateSingleAsync<T>(string collectioName, T t) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        bool isOk = await collection.UpdateAsync(t);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateSingle",
            ItemCount = 1,
            OperationInfo = $"[UpdateSingle] ==> 更新数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return isOk;
    }

    public async Task<int> LiteDbUpdateBulkAsync<T>(string collectioName, IEnumerable<T> list)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        int rcount = await collection.UpdateAsync(list);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateBulk",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateBulk",
            ItemCount = rcount,
            OperationInfo = $"[UpdateBulk] ==> 更新数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<bool> LiteDbDeleteSingleAsync<T>(string collectioName, Guid id) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        bool isOk = await collection.DeleteAsync(id);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteSingle",
            ItemCount = 1,
            OperationInfo = $"[DeleteSingle] ==> 删除数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return isOk;
    }

    public static BsonValue Serialize(Guid id) => new BsonDocument(new Dictionary<string, BsonValue>
    {
        {"_id", id }
    });

    public static Guid Deserialize(BsonValue bsonValue)
    {
        var id = bsonValue["_id"].AsGuid;
        return id;
    }

    public async Task<int> LiteDbDeleteBulkAsync<T>(string collectioName, Expression<Func<T, bool>> predicate) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        //int rcount = await collection.DeleteAllAsync();

        // ToDo 删除多个方法待定
        /*
        var bsonValues = new List<BsonValue>();
        foreach (var id in ids)
        {
            //var bsonVal = new BsonValue(id);
            //bsonValues.Add(bsonVal);

            bsonValues.Add(Serialize(id));
        }

        int rcount = await collection.DeleteManyAsync(collectioName, bsonValues.ToArray());
        */

        // C# 代码示例参考，https://csharp.hotexamples.com/examples/LiteDB/LiteDatabase/-/php-litedatabase-class-examples.html
        int rcount = await collection.DeleteManyAsync(predicate);

        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteBulk",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteBulk",
            ItemCount = rcount,
            OperationInfo = $"[DeleteBulk] ==> 删除数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;

    }
    #endregion


    #region About SQLite CRUD
    public async Task<Guid> SqliteAddSingleAsync<T>(T t) where T : BaseEntity
    {
        _sw.Start();
        var rcount = await _freeSql.Insert(t).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddSingle",
            ItemCount = rcount,
            OperationInfo = $"[AddSingle] ==> 插入数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return t.Id;
    }

    public async Task<int> SqliteAddBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity
    {
        _sw.Start();
        int rcount = await _freeSql.Insert(list).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddBulk",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddBulk",
            ItemCount = 1,
            OperationInfo = $"[AddBulk] ==> 插入数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<T> SqliteGetSingleAsync<T>(Guid id) where T : BaseEntity
    {
        _sw.Start();
        var result = await _freeSql.Select<T>().Where(x => x.Id == id).FirstAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetSingle",
            ItemCount = 1,
            OperationInfo = $"[GetSingle] ==> 查询数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<IEnumerable<T>> SqliteGetAllAsync<T>() where T : BaseEntity
    {
        _sw.Start();
        var result = await _freeSql.Select<T>().ToListAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetAll",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetAll",
            ItemCount = result.Count(),
            OperationInfo = $"[GetAll] ==> 查询数据:{result.Count()}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<bool> SqliteUpdateSingleAsync<T>(T t) where T : BaseEntity, new()
    {
        _sw.Start();

        // 推荐快照模式
        var repo = _freeSql.GetRepository<T>();
        var item = new T { Id = t.Id };
        repo.Attach(item); //此时快照 item
        t.Adapt(item);

        //bool isOk = ReferenceEquals(item, t);
        int rcount = await repo.UpdateAsync(item); //对比快照时的变化

        // 传统模式
        // int rcount = await _freeSql.Update<T>().SetSource(t).IgnoreColumns(a => new { a.Id }).ExecuteAffrowsAsync();

        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateSingle",
            ItemCount = rcount,
            OperationInfo = $"[UpdateSingle] ==> 更新数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount > 0;
    }

    public async Task<int> SqliteUpdateBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity, new()
    {
        _sw.Start();

        // 推荐快照模式
        var repo = _freeSql.GetRepository<T>();
        var items = list.Select(x => new T{ Id = x.Id });
        repo.Attach(items); //此时快照 item
        //list.Adapt(items);
        items = list;

        //foreach (var item in items)
        //{
        //    var temp = list.Where(x => x.Id == item.Id).FirstOrDefault();
        //    temp.Adapt(item);
        //}

        bool isOk = ReferenceEquals(items, list);
        int rcount = await repo.UpdateAsync(items); //对比快照时的变化

        // 传统模式
        //int rcount = await _freeSql.Update<T>().SetSource(list).IgnoreColumns(a => new { a.Id }).ExecuteAffrowsAsync();

        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateBulk",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateBulk",
            ItemCount = rcount,
            OperationInfo = $"[UpdateBulk] ==> 更新数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<bool> SqliteDeleteSingleAsync<T>(Guid id) where T : BaseEntity
    {
        _sw.Start();
        int rcount = await _freeSql.Delete<T>().Where(x => x.Id == id).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteSingle",
            ItemCount = rcount,
            OperationInfo = $"[DeleteSingle] ==> 删除数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount > 0;
    }

    public async Task<int> SqliteDeleteBulkAsync<T>(List<Guid> ids) where T : BaseEntity
    {
        _sw.Start();
        int rcount = await _freeSql.Delete<T>(ids.ToArray()).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteBulk",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteBulk",
            ItemCount = rcount,
            OperationInfo = $"[DeleteBulk] ==> 删除数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }
    #endregion
}
